-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:分区表，视图，触发器——向分区表1插入数据时，触发同时向分区表2插入数据,查询视图

--step1:建表;expect:成功
drop table if exists table_src_0013_1 cascade;
drop table if exists table_des_0013_2 cascade;
drop sequence if exists test_trigger_des_seq_0013 cascade;
create table table_src_0013_1(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id1)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
create table table_des_0013_2(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id1)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);

--step2：创建视图，函数，触发器，expect:成功
create or replace view test_trigger_src_view_0013 as select * from table_src_0013_1;
create sequence test_trigger_des_seq_0013;
create or replace function insert_func_0013()
returns trigger as $$
begin
insert into table_des_0013_2 values(nextval('test_trigger_des_seq_0013'),nextval('test_trigger_des_seq_0013'),nextval('test_trigger_des_seq_0013'));
return new;
end;
$$ language plpgsql;
/
create trigger trigger_table_src_0013_1 after insert on table_src_0013_1
for each row
execute procedure insert_func_0013();
/
--step3:向分区表1插入数据时，触发同时向分区表2插入数据，查询表，查询视图，expect:成功
select * from table_des_0013_2;
insert into table_src_0013_1 values(11,22,33);
select * from table_des_0013_2;
select * from test_trigger_src_view_0013;

--step4:环境清理;expect:成功
drop trigger trigger_table_src_0013_1 on table_src_0013_1 cascade;
drop view test_trigger_src_view_0013;
drop table table_src_0013_1 cascade;
drop table table_des_0013_2 cascade;
drop function insert_func_0013;
drop sequence test_trigger_des_seq_0013 cascade;
